from datetime import datetime
import os

import pandas as pd
import requests

headers = {
    'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:139.0) Gecko/20100101 Firefox/139.0',
    'Accept': 'application/json, text/javascript, */*; q=0.01',
    'Accept-Language': 'zh-CN,zh;q=0.8,zh-TW;q=0.7,zh-HK;q=0.5,en-US;q=0.3,en;q=0.2',
    # 'Accept-Encoding': 'gzip, deflate',
    'Origin': 'http://59.215.208.54:91',
    'Connection': 'keep-alive',
    'Referer': 'http://59.215.208.54:91/',
    'Priority': 'u=0',
}
region_code = {
    "播州-方图": "520321",
    "仁怀-方图": "520382",}
for k,v in region_code.items():
    try:
        params = {
            "org_code": v,
            "getnum_time": "2025-10-01~2025-10-31",
            "ispage": "1",
            "page": "1",
            "pagesize": "20000",
            "search_key": "",
            "ck_id": "",
            "yw_id": "",
            "data_sorce": "",
            "sys_terno_flag": "1"
        }
        response = requests.get(
            'http://59.215.208.54:81/CallYzxSourceAction/getQhjlList.do',
            params=params,
            headers=headers,
        ).json()
        list=[]
        for i in response['data']:
            list.append([i['getnum_no'],i['getnum_xname'],i['yw_name'],i['ck_name'],i['call_user_xname'],i['idc_type'],i['create_date'],i['call_status']])
        print(list)

        # 来源和叫号情况映射表
        source_mapping = {0: '预约取号', 1: '取号机'}
        status_mapping = {1: '等待中', 2: '已办理', 3: '已过号', 4: '已取消'}

        # 创建DataFrame并设置列名
        df = pd.DataFrame(list, columns=[
            '排队号',
            '取号姓名(电话)',
            '办理业务',
            '办理窗口',
            '办理人',
            '取号来源',
            '取号时间',
            '叫号情况'
        ])

        # 转换取号来源和叫号情况为文字描述
        df['取号来源'] = df['取号来源'].map(source_mapping).fillna('未知')
        df['叫号情况'] = df['叫号情况'].map(status_mapping).fillna('未知')

        # 获取当前年月日并创建目录
        current_date = datetime.now().strftime('%Y%m%d') + '取号记录查询'
        os.makedirs(current_date, exist_ok=True)  # 使用exist_ok避免重复创建错误

        # 完整文件路径

        file_path = os.path.join(current_date, f'{k}.xlsx')

        # 写入 Excel 文件并设置格式
        with pd.ExcelWriter(file_path, engine='xlsxwriter') as writer:
            df.to_excel(writer, index=False, sheet_name='Sheet1')

            # 获取工作簿和工作表对象
            workbook = writer.book
            worksheet = writer.sheets['Sheet1']

            # 设置表头样式
            header_format = workbook.add_format({
                'bold': True,
                'text_wrap': True,
                'valign': 'top',
                'fg_color': '#D7E4BC',
                'border': 1
            })

            # 应用表头样式
            for col_num, value in enumerate(df.columns.values):
                worksheet.write(0, col_num, value, header_format)

            # 自动调整列宽
            for i, col in enumerate(df.columns):
                column_width = max(df[col].astype(str).map(len).max(), len(col)) + 2
                column_width = min(column_width, 30)  # 最大列宽限制
                worksheet.set_column(i, i, column_width)

        print(f"数据已成功写入 {file_path}")
    except:print('异常了')